﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using CMP.ERP.Model;
using CMP.ERP.Helper;

namespace CMP.ERP.DAL
{
    public class SupplierDAL
    {
        /// <summary>
        /// 实例化SQLHelper对象
        /// </summary>
        SQLHelper p = new SQLHelper();

        /// <summary>
        /// 显示供应商信息
        /// </summary>
        /// <returns></returns>
        public DataTable SupplierAll()
        {
            string sql = "select Supplier_Id,Supplier_Name,Supplier_Address,Supplier_Linkman,Supplier_Phone,Supplier_Remark,Province.P_Id,Province.P_Name from Supplier join Province on Supplier.P_Id=Province.P_Id";
            return p.ExecDataTable(sql);
        }

        /// <summary>
        /// 删除供应商信息
        /// </summary>
        /// <param name="HB_Id"></param>
        /// <returns></returns>
        public int SupplierDelete(int id)
        {
            int i = p.ExecNonquery(string.Format("delete from Supplier where Supplier_Id={0}", id));
            return i;
        }

        /// <summary>
        /// 添加供应商信息
        /// </summary>
        /// <param name="h"></param>
        /// <returns></returns>
        public int AddSupplier(Supplier s)
        {
            int i = p.ExecNonquery(string.Format("insert into Supplier(Supplier_Name,Supplier_Address,Supplier_Linkman,Supplier_Phone,Supplier_Remark,P_Id) values('{0}','{1}','{2}','{3}','{4}',{5})",s.Supplier_Name,s.Supplier_Address,s.Supplier_Linkman,s.Supplier_Phone,s.Supplier_Remark,s.P_Id));
            return i;
        }

        /// <summary>
        /// 根据供应商编号找到供应商信息
        /// </summary>
        /// <param name="Client_Id"></param>
        /// <returns></returns>
        public DataTable GetSupplier(int id)
        {
            DataTable dt = p.ExecDataTable(string.Format("select *from Supplier WHERE Supplier_Id ='{0}'", id));
            return dt;
        }

        /// <summary>
        /// 修改供应商信息
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public int UptSupplier(Supplier supplier)
        {
            string sql = string.Format("update Supplier set Supplier_Name='{0}',Supplier_Address='{1}',Supplier_Linkman='{2}',Supplier_Phone='{3}',Supplier_Remark='{4}',P_Id={5} where Supplier_Id='{6}'", supplier.Supplier_Name, supplier.Supplier_Address, supplier.Supplier_Linkman, supplier.Supplier_Phone, supplier.Supplier_Remark, supplier.P_Id,supplier.Supplier_Id);
            return p.ExecNonquery(sql);
        }

        /// <summary>
        /// 根据供应商名称进行查询
        /// </summary>
        /// <param name="HB_Name"></param>
        /// <returns></returns>
        public DataTable SearchSupplier(string Supplier_Name)
        {
            string sql = string.Format("select Supplier_Id,Supplier_Name,Supplier_Address,Supplier_Linkman,Supplier_Phone,Supplier_Remark,Province.P_Id,Province.P_Name from Supplier join Province on Supplier.P_Id=Province.P_Id where Supplier_Name like '%{0}%'", Supplier_Name);
            return p.ExecDataTable(sql);
        }

    }
}
